<?php
class omeanalysts_mdl_ome_goodsale extends dbeav_model{

    public function searchOptions()
    {
        return array(
            'bn'=>$this->app->_('货号'),
        );
    }
    
    public function count($filter=null){
         $filter_sql = $this->_filter($filter);
         $sql = 'SELECT count(*) FROM (SELECT si.product_id FROM sdb_ome_sales_items AS si '.
                $filter_sql['join_filter'].
                ' WHERE si.sales_type=\'0\''.$filter_sql['base_filter'].' GROUP BY si.product_id) AS c ';
         $tmp = $this->db->count($sql);
         return $tmp;
    }

    public function getlist($cols='*', $filter=array(), $offset=0, $limit=-1, $orderType=null){
         $orderType = $orderType ? $orderType : $this->defaultOrder;
         $filter_sql = $this->_filter($filter);

         $sql = 'SELECT si.bn,si.name,si.product_id,SUM(si.nums) AS nums,SUM(si.cost_amount) AS cost_amount,SUM(si.amount) AS amount,SUM(si.discount_amount) AS discount_amount,SUM(si.additional_costs) AS additional_costs,SUM(si.sales_amount) AS sales_amount FROM sdb_ome_sales_items AS si '.
         $filter_sql['join_filter'].  
         ' WHERE si.sales_type=\'0\''.$filter_sql['base_filter'].' GROUP BY si.product_id';
         if($orderType){
            $sql.=' ORDER BY '.(is_array($orderType)?implode($orderType,' '):$orderType);
         }else{
            $sql.=' ORDER BY si.sales_amount DESC ';
         }

         $data = $this->db->selectLimit($sql,$limit,$offset);
         if($data){
            foreach($data as &$value){
                #商品信息
                $sql = ' SELECT t.name as goods_type,b.brand_name,p.name as product_name FROM sdb_ome_products AS p,sdb_ome_goods AS g '
                       .' LEFT JOIN sdb_ome_goods_type AS t ON g.type_id=t.type_id '
                       .' LEFT JOIN sdb_ome_brand AS b ON b.brand_id=g.brand_id '
                       .' WHERE p.product_id='.$value['product_id'].' AND p.goods_id=g.goods_id';
                $g = $this->db->selectrow($sql);
                $value['goods_type'] = $g['goods_type'];
                $value['brand_name'] = $g['brand_name'];
                if (empty($value['name'])){
                    $value['name'] = $g['product_name'];
                }
				$value['gross_sales'] = $value['sales_amount'] - $value['cost_amount'];
				$value['gross_sales_rate'] = (ROUND($value['gross_sales']/$value['sales_amount'],4)*100)."%";
                #平均成交价格
                $value['sales_price'] = round($value['sales_amount'] / $value['nums'],3);
            }
         } 

         return $data;
    }

    public function _filter($filter,$tableAlias=null,$baseWhere=null){
        $where = array();
        #发货时间
        if(isset($filter['time_from']) && $filter['time_from']){
            $where['base_filter'][] = ' si.sales_time >='.strtotime($filter['time_from']);
        }
        if(isset($filter['time_to']) && $filter['time_to']){
            $where['base_filter'][] = ' si.sales_time <'.(strtotime($filter['time_to'])+86400);
        }
		#仓库
        if(isset($filter['branch_id']) && $filter['branch_id']){
            $where['base_filter'][] = ' si.branch_id =\''.addslashes($filter['branch_id']).'\'';
        }
        #销售店铺
        if(isset($filter['shop_id']) && $filter['shop_id']){
            $where['base_filter'][] = ' si.shop_id =\''.addslashes($filter['shop_id']).'\'';
        }
        #货号
        if(isset($filter['bn']) && $filter['bn']){
            $where['base_filter'][] = " si.bn ='".$filter['bn']."'";
        }
        #商品品牌
        if(isset($filter['brand_id']) && $filter['brand_id']){
            $where['join_filter']['sdb_ome_goods'][] = ' g.brand_id=\''.$filter['brand_id'].'\'';
        }
        #商品类型
        if(isset($filter['type_id']) && $filter['type_id']){
            $where['join_filter']['sdb_ome_goods'][] = ' g.type_id=\''.$filter['type_id'].'\'';
        }
        
        #拼接SQL查询
        $where_sql = array();
        if (isset($where['base_filter'])){
            $where_sql['base_filter'] = ' AND '.implode(' AND ',$where['base_filter']);
        }
        if (isset($where['join_filter'])){
            foreach ($where['join_filter'] as $join_table=>$on_field){
                $on_where = implode(' AND ',$on_field);
                switch($join_table){
                    case 'sdb_ome_goods':
                        $where_sql['join_filter'][$join_table] = ' JOIN (sdb_ome_products AS p,sdb_ome_goods AS g) ON si.product_id=p.product_id AND p.goods_id=g.goods_id AND '.$on_where;
                        break;
                }
            }
            $where_sql['join_filter'] = implode(' ',$where_sql['join_filter']);
        }
        return $where_sql;
    }
    
    
    public function get_schema(){
        $schema['columns'] = array(
            'goods_type' =>
            array (
              'type' => 'varchar(64)',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '商品类型',
              'order' => '1',
              'width' => '100',
            ),
            'brand_name' =>
            array (
              'type' => 'varchar(64)',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '商品品牌',
              'order' => '2',
              'width' => '100',
            ),
            'bn' =>
            array (
              'type' => 'varchar(32)',
              'required' => true,
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '货号',
              'order' => '3',
              'width' => '130',
            ),
            'name' =>
            array (
              'type' => 'varchar(200)',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '商品名称',
              'order' => '4',
              'width' => '300',
            ),
            'nums' =>
            array (
              'type' => 'mediumint',
              'required' => true,
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '数量',
              'order' => '5',
              'width' => '50',
            ),
            'cost_amount' =>
            array (
              'type' => 'money',
             'default_in_list'=>true,
			'in_list'=>true,
              'default' => 0,
              'label' => '成本金额',
              'order' => '6',
              'width' => '75',
            ),
			'gross_sales' =>
			array (
			  'type' => 'money',
			  'default' => 0,
			  'label' => '销售毛利',
			  'comment' => '商品的销售毛利',
			  'in_list'=>true,
			  'width' => '75',
			),
			'gross_sales_rate' =>
			array (
			  'type' => 'decimal(10,2)',
			  'default' => 0,
			  'label' => '毛利率',
			  'comment' => '商品的毛利率',
			  'in_list'=>true,
			  'width' => '75',
			),
            'amount' =>
            array (
              'type' => 'money',
              'default' => '0',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '原始金额',
              'order' => '7',
              'width' => '75',
            ),
            'discount_amount' =>
            array (
              'type' => 'money',
              'default' => '0',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '优惠金额',
              'order' => '8',
              'width' => '75',
            ),
            'additional_costs' =>
            array (
              'type' => 'money',
              'default' => '0',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '附加费',
              'order' => '9',
              'width' => '75',
            ),
            'sales_amount' =>
            array (
              'type' => 'money',
              'default' => '0',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '成交金额',
              'order' => '10',
              'width' => '75',
            ),
            'sales_price' =>
            array (
              'type' => 'money',
              'default' => '0',
              'default_in_list'=>true,
              'in_list'=>true,
              'label' => '平均成交价格',
              'order' => '11',
              'orderby' => false,
              'width' => '100',
            ),
        );
        foreach ($schema['columns'] as $col=>$detail){
            if ($detail['in_list']){
                $schema['in_list'][] = $col;
            }
            if ($detail['default_in_list']){
                $schema['default_in_list'][] = $col;
            }
        }
        return $schema;
    }

    /* omeio export */

    //配置信息
    public function export_params(){

        $export_filter = $this->export_filter;
        if ($filter = unserialize($_POST['params'])) {
            $dates = $filter['time_from'].'至'.$filter['time_to'];
        }
        if($export_filter['bn']) $filter['bn'] = $export_filter['bn'];
        $params = array(
            'filter' => $filter,
            'limit' => 2000,
            'get_data_method' => 'get_goodsale_data',
            'single'=> array(
                'goodsale'=> array(
                    'filename' => $dates.'商品销售汇总表',
                ),
            ),
        );
        return $params;
    }

    //商品销售汇总title
    public function get_goodsale_data_title(){
        $title['goodsale'] = array(
            '*:商品类型',
            '*:商品品牌',
            '*:货号',
            '*:商品名称',
            '*:数量',
            '*:原始金额',
            '*:优惠金额',
            '*:附加费',
            '*:成交金额',
            '*:成本金额',
            '*:销售毛利',
            '*:毛利率',
            '*:平均成交价格',
        );
        return $title;
    }

    //商品销售汇总
    public function get_goodsale_data($filter,$offset,$limit,&$data){
        $goodsaleMdl = &$this->app->model('ome_goodsale');
        $goodsale = $goodsaleMdl->getList('*',$filter,$offset,$limit);
        if(!empty($goodsale)){
            foreach($goodsale as $v){
                $data['goodsale'][] = array(
                    '*:商品类型' => $v['goods_type'],
                    '*:商品品牌' => $v['brand_name'],
                    '*:货号' => $v['bn'],
                    '*:商品名称' => $v['name'],
                    '*:数量' => $v['nums'],
                    '*:原始金额' => $v['amount'],
                    '*:优惠金额' => $v['discount_amount'],
                    '*:附加费' => $v['additional_costs'],
                    '*:成交金额' => $v['sales_amount'],
					'*:成本金额' => $v['cost_amount'],
					'*:销售毛利' => $v['sales_amount']-$v['cost_amount'],
					'*:毛利率' => (ROUND(($v['sales_amount']-$v['cost_amount'])/$v['sales_amount'],4)*100)."%",
                    '*:平均成交价格' => $v['sales_price'],
                );
            }
        }
    }

}